Database systems are used in a wide range of enterprises for the storage and manipulation of data. The data could, for example, relate to a customer's personal information and information on the products and services that they have purchased. Relational database systems such as IBM DB2 UDB are used to store this type of data.
Database systems depend on stored metadata for the normal operation of a database instance. A database system uses this information when serving user requests, in the form of either DML (data manipulation language) statements or calls from database utilities. In DB2 UDB, this metadata information is stored as database tables and views called system catalog views. Application developers can use metadata to manipulate a database object when developing applications. The DB2 database manager uses system catalog views and database instances while serving requests from users. Data management is provided using a structured query language SQL; this provides data query and update, schema creation and modification, and data access control.
Initially, relational database systems operated with embedded or static SQL. Static SQL statements are written within an application program and are pre-processed by a database dependent SQL preprocessor before the application is compiled. In the preprocessing stage, a DB2 package is created which is bound outside the compile process. The bind process determined the access path.
Open Database Connectivity ODBC facilitated the creation of dynamic SQL because it allowed SQL to be created that did not have to be embedded into the application programming language, and pre-compilation was no longer required.
Therefore static SQL statements in an application do not change at runtime and can be hard-coded into the application. Dynamic SQL statements are constructed at runtime to allow users to enter their own queries. Thus, the dynamic SQL statements cannot be hard-coded into the application. Static SQL provides performance advantages over dynamic SQL because static SQL is pre-processed, which means the statements are parsed, validated, and optimized only once.
In many applications, database systems contain millions of data tables and there is an ongoing need to be able to create queries which copy data from one environment to another. This can be as a result of the need to test an improvement to the database or a new product before live use.
Testing requires data created within a test environment. This is usually data that is extracted from an alternative environment and loaded into the test environment which is often a production environment. In many cases, because of the complex inter-relationships between data in the database, the tester may not know exactly which data is required. The data chosen by the tester may have underlying data requirements that are not apparent. Where a test is performed on incomplete data an error will occur. The problem with errors is that it is not immediately apparent to the tester whether the error has arisen from a failure to properly specify the required data or whether there is a more fundamental problem with the test itself.
Another issue is that of CPU capacity. In many cases, organisations will buy database services from a supplier; this will include the purchase of processing capacity. The ability to copy data from one environment to another requires a user to create their own database queries which have a large draw on the available processing capacity. In practice, this problem is controlled by carefully managing the queries that are created by users and running the queries at times when the system processing capacity is underused such as in the evening or at weekends. This is inconvenient for the users and slows down the development process considerably. Product service